clear
clear matrix
clear mata
program drop _all
set more off
set emptycells drop
set maxvar 100000

#delimit ;
cap log close;


#delimit cr

tempfile main


cd "<directory>"

*The code on lines 29-327 repeats the following steps for years 2000-2017 of the raw Trepp data:;
*	1. Read in raw data file;
*	2. Drop variables that are unnecessary for the analysis;
*	3. Create and format date variables;
*	4. Remove observations that are unnecessary for the analysis;
*	5. Save an intermediate/interim dataset that is called in the append commands later;

u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa step1data_base,replace

forv i=2001(1)2015 {
	u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
	drop proptypenorm proptypelong *typecd
	compress
	ap using step1data_base,force
	sa step1data_base,replace
}


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd

foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd

foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd

foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace


u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace



u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace



u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace



u "<directory>",clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-v111 units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate{
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
ap using step1data_base,force
sa step1data_base,replace



u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
tostring guarantorandsponsor,replace force
replace guarantorandsponsor="" if guarantorandsponsor=="."
compress
sa step1data2_base,replace

forv i=2001(1)2015 {
	u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
	tostring guarantorandsponsor,replace force
	compress
	ap using step1data2_base,force
	sa step1data2_base,replace
}
u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace

u dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus using "<directory>",clear
compress
ap using step1data2_base,force
sa step1data2_base,replace



*The code on lines 335-1546:;
*	1. Reads in raw Trepp files for 2018-2020;
*	2. Cleans the 2018-2020 data in a manner analagous to the code on lines 29-327;

tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan_201801.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f01',replace

import delim loan_201802.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f02',replace

import delim loan_201803.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f03',replace

import delim loan_201804.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f04',replace

import delim loan_201805.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f05',replace

import delim loan_201806.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f06',replace

import delim loan_201807.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f07',replace

import delim loan_201808.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd

foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f08',replace

import delim loan_201809.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f09',replace

import delim loan_201810.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd

foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f10',replace

import delim loan_201811.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596

drop proptypenorm proptypelong *typecd
compress
sa `f11',replace

import delim loan_201812.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f12',replace


u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace


tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan2_201801.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f01',replace


import delim loan2_201802.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f02',replace

import delim loan2_201803.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f03',replace

import delim loan2_201804.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f04',replace

import delim loan2_201805.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f05',replace

import delim loan2_201806.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f06',replace

import delim loan2_201807.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f07',replace

import delim loan2_201808.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f08',replace

import delim loan2_201809.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f09',replace

import delim loan2_201810.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f10',replace

import delim loan2_201811.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f11',replace

import delim loan2_201812.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f12',replace

u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace



tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan_201901.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f01',replace

import delim loan_201902.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f02',replace

import delim loan_201903.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f03',replace

import delim loan_201904.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f04',replace

import delim loan_201905.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f05',replace

import delim loan_201906.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f06',replace

import delim loan_201907.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f07',replace

import delim loan_201908.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f08',replace

import delim loan_201909.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f09',replace

import delim loan_201910.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f10',replace

import delim loan_201911.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f11',replace

import delim loan_201912.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f12',replace


u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace



tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan2_201901.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f01',replace


import delim loan2_201902.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f02',replace

import delim loan2_201903.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f03',replace

import delim loan2_201904.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f04',replace

import delim loan2_201905.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f05',replace

import delim loan2_201906.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f06',replace

import delim loan2_201907.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f07',replace

import delim loan2_201908.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f08',replace

import delim loan2_201909.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f09',replace

import delim loan2_201910.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f10',replace

import delim loan2_201911.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f11',replace

import delim loan2_201912.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f12',replace

u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace




tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan_202001.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f01',replace

import delim loan_202002.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f02',replace

import delim loan_202003.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f03',replace

import delim loan_202004.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f04',replace

import delim loan_202005.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f05',replace

import delim loan_202006.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f06',replace

import delim loan_202007.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f07',replace

import delim loan_202008.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f08',replace

import delim loan_202009.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f09',replace

import delim loan_202010.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f10',replace

import delim loan_202011.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f11',replace

import delim loan_202012.csv,delim(",") clear
drop transidtrustee groupidtrustee paidthrudate curindexrate matdatetrustee-nextpmtchgdate pppenalties pppenint mraser-totalothadvance modrate-mrappvalue expresolutiondate-noincfind priorfyncf-ctltrustee lat-longitude units-maturityloanperunit securrevenues-securexpenses noitypecd-cftypecd balloonltv-monthsdelinquent origborrowername hotelfranchise securitytype-totreservebal spcsrvfeeamt-dlqderivedcd
foreach var of varlist origdate fcdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen origyear=year(origdate)
gen origmonth=month(origdate)
gen origym=ym(origyear,origmonth)
keep if origym>=504 & origym<=596
drop proptypenorm proptypelong *typecd
compress
sa `f12',replace


u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace



tempfile f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12

import delim loan2_202001.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f01',replace


import delim loan2_202002.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f02',replace

import delim loan2_202003.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f03',replace

import delim loan2_202004.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f04',replace

import delim loan2_202005.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f05',replace

import delim loan2_202006.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f06',replace

import delim loan2_202007.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f07',replace

import delim loan2_202008.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f08',replace

import delim loan2_202009.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f09',replace

import delim loan2_202010.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f10',replace

import delim loan2_202011.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f11',replace

import delim loan2_202012.csv,delim(",") clear
drop if poolnum==""
so dosname masterloanidtrepp distdate notenum
duplicates tag dosname masterloanidtrepp distdate ,g(dup)
su dup,d
foreach var of varlist distdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
keep dosname masterloanidtrepp distdate disposedamount dispositiondt dispositiontype dispositionsubtype poolnum guarantorandsponsor liqsalesprice derivedloanstatus
sa `f12',replace

u `f01',clear
ap using `f02' `f03' `f04' `f05' `f06' `f07' `f08' `f09' `f10' `f11' `f12',force
compress
sa "<directory>",replace






*The code on lines 1560-1747... accomplishes the following steps:;
*	1. Create and clean additional date variables;
*	2. Define property type variable;
*	3. Create loan characteristics;
*	4. Create transfer and workout variables;
*	5. Merge additional loan characteristics;

u step1data_base,clear
ap using "<directory>"
ap using "<directory>"
ap using "<directory>"
drop address city state zip 
sa `main',replace

drop if poolnum==""
so dosname masterloanidtrepp distdate
duplicates tag dosname masterloanidtrepp distdate,g(dup)
su dup,d
so dosname masterloanidtrepp poolnum distdate
duplicates drop dosname masterloanidtrepp distdate,force
foreach var of varlist distdate spcsrvtransfer unschedprindate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen distyear=year(distdate)
gen distmonth=month(distdate)
gen distym=ym(distyear,distmonth)
gen ym=distym
foreach var of varlist watchlistdate {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}
gen watchlistyear=year(watchlistdate)
gen watchlistmonth=month(watchlistdate)
gen watchlistym=ym(watchlistyear,watchlistmonth)
egen mindistym=min(distym),by(dosname masterloanidtrepp)
drop if mindistym>596


gen myproptype=""
replace myproptype="Multifamily" if proptype=="Multifamily" | proptype=="Multi-Family" | proptype=="Mulitfamily" | proptype=="Mulit Family" | proptype=="multifamily" | proptype=="MF"
replace myproptype="Office" if proptype=="Office" | strmatch(proptype,"*office*")
replace myproptype="Retail" if proptype=="Retail" | strmatch(proptype,"*Retail*") | strmatch(proptype,"*retail*") | strmatch(proptype,"*mall*") | strmatch(proptype,"*Shopping*") | strmatch(proptype,"*shopping*")
replace myproptype="Industrial" if proptype=="Industrial" | proptype=="Industrial/W'hse" | proptype=="Industrial/Warehouse" | proptype=="industrial/warehouse" | strmatch(proptype,"*warehouse*") | strmatch(proptype,"*Warehouse*")
replace myproptype="Hospitality" if proptype=="Hospitality" | proptype=="Hotel" | proptype=="hotel" | proptype=="Lodging" | proptype=="Extended Stay" | proptype=="Banquet Hall" | proptype=="Golf Course" | proptype=="Restaurant" | strmatch(proptype,"*Restaurant*") | strmatch(proptype,"*RESTAURANT*") | strmatch(proptype,"*restaurant*") | strmatch(proptype,"*Lodging*") | strmatch(proptype,"*Hotel*")
replace myproptype="Other" if proptype=="Other"
replace myproptype="Other" if myproptype=="" & proptype!=""
egen propid=group(myproptype)


egen firstdistdate=min(distdate),by(dosname masterloanidtrepp)
form firstdistdate %td
gen orig_ltv = ltv if distdate==firstdistdate
gen orig_obal = obal if distdate==firstdistdate
gen orig_beginbal = beginbal if distdate==firstdistdate
gen orig_dscrncf = dscrncf if distdate==firstdistdate
gen orig_dscrnoi = dscrnoi if distdate==firstdistdate
so dosname masterloanidtrepp distdate 
by dosname masterloanidtrepp: gen firstnoi=1 if dscrnoi!=. & dscrnoi[_n-1]==.
by dosname masterloanidtrepp: gen firstncf=1 if dscrncf!=. & dscrncf[_n-1]==.
gen firstdatenoi=distdate if firstnoi==1
gen firstdatencf=distdate if firstncf==1
egen minfirstnoi=min(firstdatenoi),by(dosname masterloanidtrepp)
egen minfirstncf=min(firstdatencf),by(dosname masterloanidtrepp)
form minfirstnoi minfirstncf %td
gen diffnoi=(minfirstnoi-firstdistdate)/365
gen diffncf=(minfirstncf-firstdistdate)/365
gen orig_dscrncf2 = dscrncf if distdate==minfirstnoi
gen orig_dscrnoi2 = dscrnoi if distdate==minfirstncf
egen maxorig_dscrncf2=max(orig_dscrncf2),by(dosname masterloanidtrepp)
egen maxorig_dscrnoi2=max(orig_dscrnoi2),by(dosname masterloanidtrepp)
drop orig_dscrncf2 orig_dscrnoi2 firstdatenoi firstdatencf minfirstnoi minfirstncf diffnoi diffncf
ren maxorig_dscrncf2 orig_dscrncf2
ren maxorig_dscrnoi2 orig_dscrnoi2
egen maxorigloanbal=max(origloanbal),by(dosname masterloanidtrepp)
drop if maxorigloanbal==.
drop origloanbal
ren maxorigloanbal origloanbal
gen balratio=obal/origloanbal
gen age=distym-origym
drop if age<0
egen maxorig_ltv=max(orig_ltv),by(dosname masterloanidtrepp)
drop orig_ltv
ren maxorig_ltv orig_ltv


egen firstspcsrvtransfer=min(spcsrvtransfer),by(dosname masterloanidtrepp)
form firstspcsrvtransfer %td
gen firstspcsrvtransfer_year=year(firstspcsrvtransfer)
gen firstspcsrvtransfer_month=month(firstspcsrvtransfer)
gen transfer=1 if distyear==firstspcsrvtransfer_year & distmonth==firstspcsrvtransfer_month
replace transfer=0 if transfer==.
replace transfer=. if firstspcsrvtransfer!=. & ((distyear==firstspcsrvtransfer_year & distmonth>firstspcsrvtransfer_month) | (distyear>firstspcsrvtransfer_year))
egen evertransfer=max(transfer),by(dosname masterloanidtrepp)
egen lastspcsrvtransfer=max(spcsrvtransfer),by(dosname masterloanidtrepp)
form lastspcsrvtransfer %td
gen morethanonetransfer=1 if firstspcsrvtransfer!=lastspcsrvtransfer
drop if morethanonetransfer==1
drop lastspcsrvtransfer morethanonetransfer
gen workoutdate=distdate if workoutcode!=.
egen firstworkoutdate=min(workoutdate),by(dosname masterloanidtrepp)
form firstworkoutdate %td
gen firstworkoutmethod=workoutcode if workoutdate==firstworkoutdate
egen maxfirstwomethod=max(firstworkoutmethod),by(dosname masterloanidtrepp)
drop firstworkoutmethod
ren maxfirstwomethod firstworkoutmethod
sa `main',replace

tempfile newcodes
so dosname masterloanidtrepp distdate
collapse (max) distdate,by(dosname masterloanidtrepp workoutcode)
drop if workoutcode==.
drop if workoutcode==8 | workoutcode==9 | workoutcode==13
so dosname masterloanidtrepp distdate
by dosname masterloanidtrepp: gen counter=_n
egen maxcount=max(counter),by(dosname masterloanidtrepp)
gen lastworkoutmethod=workoutcode if counter==maxcount
egen maxlastworkoutmethod=max(lastworkoutmethod),by(dosname masterloanidtrepp)
drop lastworkoutmethod
ren maxlastworkoutmethod lastworkoutmethod
gen workoutdate=distdate if workoutcode!=.
egen lastworkoutdate=max(workoutdate),by(dosname masterloanidtrepp)
form lastworkoutdate %td
drop workoutdate
gen last_is_reo=1 if lastworkoutmethod==7
gen maxcountless1=maxcount-1
gen else_to_reo=1 if counter==maxcountless1 & workoutcode!=7 & last_is_reo==1
gen secondtolastwomethod=workoutcode if else_to_reo==1
egen maxstlwom=max(secondtolastwomethod),by(dosname masterloanidtrepp)
drop secondtolastwomethod
ren maxstlwom secondtolastwomethod
gen secondlastworkoutdate=distdate if else_to_reo==1
egen maxsecondlastworkoutdate=max(secondlastworkoutdate),by(dosname masterloanidtrepp)
drop secondlastworkoutdate
ren maxsecondlastworkoutdate secondlastworkoutdate
form secondlastworkoutdate %td
collapse (max) lastworkoutmethod secondtolastwomethod lastworkoutdate secondlastworkoutdate else_to_reo,by(dosname masterloanidtrepp)
gen new_lastworkoutmethod=lastworkoutmethod if lastworkoutmethod!=7
replace new_lastworkoutmethod=secondtolastwomethod if lastworkoutmethod==7
gen new_lastworkoutdate=lastworkoutdate if lastworkoutmethod!=7
replace new_lastworkoutdate=secondlastworkoutdate if lastworkoutmethod==7
form new_lastworkoutdate %td
ren lastworkoutmethod orig_lastworkoutmethod
ren lastworkoutdate orig_lastworkoutdate
ren new_lastworkoutmethod lastworkoutmethod
ren new_lastworkoutdate lastworkoutdate
sa `newcodes',replace

u `main',clear
merge m:1 dosname masterloanidtrepp using `newcodes'
drop if _merge==2
drop _merge

egen firstprepaydate=min(distdate) if unschedprindate!=.,by(dosname masterloanidtrepp)
egen lastprepaydate=max(distdate) if unschedprindate!=.,by(dosname masterloanidtrepp)
form firstprepaydate lastprepaydate %td
gen firstprepaycd=prepaycd if firstprepaydate==distdate
gen lastprepaycd=prepaycd if lastprepaydate==distdate
sa `main',replace



tempfile loan2
u step1data2_base,clear

foreach var of varlist distdate dispositiondt {
	tostring `var',g(c`var')
	gen `var'2=date(c`var',"YMD")
	form `var'2 %td
	drop `var' c`var'
	ren `var'2 `var'
}

ap using "<directory>"
ap using "<directory>"
ap using "<directory>"
so dosname masterloanidtrepp poolnum distdate
duplicates drop dosname masterloanidtrepp distdate,force
sa `loan2',replace


u `main',clear
merge 1:1 dosname masterloanidtrepp distdate using `loan2'
drop if _merge==2
drop _merge
compress


sa "<directory>",replace




